/************************************************************************
*	Description: The program uses manily the REAL-registers to compute a* 
* 				 series of data sets on individual and loan level 		*
*				 containing detailed information on the mortgage loans 	*
*	     	 	 of the Danish population 								*
*																		*
*	Structure:															*
*		1) Libraries are defined										*
*		2) Parameters for start and end year are computed				*
*		3) Correspondance between kom (municipality) and bopikom (adress)*
*		4) Own calculated variables are added. Summation on real estate *
*		   level. Only primary (ejerbolig) and secondary residence 		*
*		   (fritidsbolig) are included.									*
*		5) Mortgage data set on individual and loan level are defined.	*
*		   Only primary (ejerbolig) and secondary residence 			*
*		   (fritidsbolig) are included.									*
*																		*
*	Parameters:															*
*		Startaar: 	Sets first year for output data sets				*
*		Slutaar:	Sets last year fir output data sets					*
*																		*
*	Input: 																*
*		raw.bef			Population										*
*		raw.bol			Register of exact housing numbers				*
*		raw.real		Housing finance									*
*																		*
*	Output: SAS data sets: 												*
*		"databib.Real"		All mortgage loans including some			* 
*			corrections and calculated variables.						*
*		"databib.Real2_"	Mortgage loans for primary and secondary 	*
*			residence and calculated variables.							*
*		"databib.realkreditlaan" Mortgage data on loan level on all 	*
*			mortgage loans for primary and secondary residence.			*
*		"databib.RK_Individdata" Mortgage data on individual level for 	*
*			all Danes with a mortgage loan.								*
*																		*
*																		*
************************************************************************/

/************************************************************************
* 1) Libraries are defined												*
************************************************************************/

libname databib "E:/ProjektDB/706172/Workdata/706172/Marie/Husholdningsprojekt/Data/REAL";
libname raw   "E:/ProjektDB/706172/DST_rawdata_706172";
libname fmt  '\\srvfsenas1\data\formater\SAS formater i Danmarks Statistik\FORMATKATALOG';
options fmtsearch=(fmt.times_personstatistik fmt.brancher fmt.uddannelser fmt.geokoder work egnefmt);
options compress=yes; 

dm 'cle log';

/************************************************************************
* 2) Parameters for start and end year are computed (Startaar and Slutaar)							*
************************************************************************/

/* Start and end year for REAL-registers */
%let startaar_real = 2009;
%let slutaar_real = 2016;

/* Start year for the family data set */

/* Dummy variables for each year until slutaar_real are defined,
which includes fx information whether a person has taken a mortgage loan 
within the year. */

%let startaar = 1998;

/* Variable used to account for expiration of an intrest-only period. Should not be changed. */
%let AFUdloebslut = %eval(&slutaar_real + 10); 


/************************************************************************
* 3) This part of the program creates a correspondance between two 		*
*	 combinations of variables: KOM+BOPIKOM (municipality and adress)	*
*	 and KOM+EJDNR (municipality and a key for municipality number 		*
*	 before and after the merger of municipalities (2007), which also	* 
*	 contains property number, EJDNR)									*
* 																		*
*	 Population data (BEF) containes KOM and BOPIKOM, which identifies 	*
*	 a unique residential address. The property number is not contained *
*	 in BEF, so information on the property cannot be linked.			*
*																		*
* Note: Information in BEF and BBR is the information per January 1st.  *
* The calculated data sets should contain the information per December  *
* 31st. To account for this, the calculated data sets are named 		*
* corresponding to the year for BEF minus 1. 							*
* That is, the output data set bopikom_ejdnr_2010 contains information  *
* per ultimo 2010 and is based on information from BEF2011 and BOL2011  * 
************************************************************************/

*Defines a macro CHECKIT to check if a data set exists for all years 
 needed to compute output data sets;
%macro checkit(dsn);
	%do aar=&startaar_real %to &slutaar_real;
		%let aar_lead=%eval(&aar+1);
		%if %sysfunc(exist(raw.&dsn&aar_lead)) = 1 %then %do;
			data &dsn&aar_lead;
				set raw.&dsn&aar_lead;
			run;
			data _null_;
			file print;
			put "Data set &dsn&aar_lead already exists.";
			run;
		%end;
		%else %do;
			data &dsn&aar_lead;
				set raw.&dsn&aar;
			run;
			data _null_;
			file print;
			put "Data set &dsn&aar_lead does not exists. Data set from the latest year is inserted.";
			run;
		%end;
	%end;
%mend;

* Runs the macro to control existence of AKM and IDAP;
%checkit(bol); *Checks BOL;


* Defines and runs the macro bopikom.
The macro generates the data sets bopikom_ejdnr_&aar by merging BEF and BOL for &aar+1 by KOM and BOPIKOM;
%macro bopikom;
		%DO aar=&startaar_real %TO &slutaar_real;
			%let aar_lead=%eval(&aar+1);

			data bef&aar_lead (drop = kom_num);
				set raw.bef&aar_lead (rename = (kom = kom_num) keep=pnr kom bopikom);
				length kom $3.; *Sets length of the character to 3;
				kom = kom_num;
			run;

			proc sort data=bef&aar_lead;
				by kom bopikom;
			run;

			data bol&aar_lead;
				set bol&aar_lead (keep= kom bopikom ejdnr);
			run;

			proc sort data=bol&aar_lead;
				by kom bopikom ejdnr;
			run;

			data bol&aar_lead;
				set bol&aar_lead;
				by kom bopikom;
				if first.bopikom; *Drops duplicates - first.variable (here bopikom) takes the value 1 for the first observation in the by group. 0 for all others;
			run;
		
			data bopikom_ejdnr_&aar;
				merge bef&aar_lead (in=a)
			   		  bol&aar_lead;
				by kom bopikom;
				if a; *keeps observations from BEF only;
				kom_ejdnr=put(kom,3.)||ejdnr; *Defines kom_ejdnr by concatenating KOM (format 3.) with ejdnr;
			run;

			proc sort data=bopikom_ejdnr_&aar;
				by pnr;
			run;

%end;
%mend;
%bopikom;		

/* Delets work data sets */
%macro slette_bol_bef;
	%do aar = &startaar_real %to &startaar_real;
	%let aar_lead = %eval(&aar + 1);
		proc datasets library = work;
			delete bol&aar_lead bef&aar_lead;
		run;
	%end;
%mend;
%slette_bol_bef;

/************************************************************************
* 3) The following macros corrects some mistakes in the raw data on		*
*	 mortgage loans (REAL registers).									*
* 	 The corrected data sets are saved in the databib library as		*
* 	 real&aar															*
************************************************************************/

/*The macro real generates the variable miks_ejd, a dummy accounting for the same loan
being used to finance different property.

The variable ANDEL (share) is computed, accounts for the share of the 
loan the borrower is liable. 

As before, the variables KOM_EJDNR is computed

A series of other variables (dummies) describing the loan are defined.
They are labled in the end of the macro*/

%macro real;
	%DO aar=&startaar_real %TO &slutaar_real;

		DATA real&aar;
			SET raw.real&aar;
			LABEL hovedknt=' ';
			LABEL hovedobl=' ';
			LABEL restknt=' ';
			LABEL restobl=' ';
			LABEL terminer=' ';
			LABEL LTV=' ';
			laandato_ny = put(laandato, DDMMYYN8.); *Changes format;
			kom_ejdnr=put(ejdkom,3.)||ejdnr; *Defines kom_ejdnr by concatenating EJDKOM (format 3.) with EJDNR (similar to before);
			drop laandato;
			rename laandato_ny = laandato;
		RUN;

		PROC SORT DATA=real&aar noduplicates;
			BY laannr ejendom;
		RUN;

		/*Computes miks_ejendomsvariabel (mix real estate variable).*/
		DATA real_miks&aar (DROP = ejendom);
			SET real&aar (KEEP = ejendom laannr);
			IF ejendom NE lag1(ejendom) AND laannr = lag1(laannr);
			miks_ejd=1; *Accounts for if the same loan is used to finance different real estates;
			BY laannr;
		RUN;


		/* Computes the variable ANDEL (share)*/
		DATA real&aar;
			SET real&aar;
			count + 1; 
			BY LAANNR;
			IF first.LAANNR THEN count = 1;
		RUN;

		/*Computes the variable ANDEL (share) by counting the number of pnrs associated 
		  with the loan number. Only the last of the observations are saved in this data set.
		  That is, if two people cosign for a loan, only the second will appear in this data set */
		DATA real1_&aar;
			SET real&aar;
			BY LAANNR;
			andel=1/count;
			IF last.LAANNR THEN output; 
		RUN;

		/*Merges data sets by LAANNR (loan number)*/
		DATA real&aar;
			MERGE 	real&aar
					real1_&aar (KEEP= laannr andel)
					real_miks&aar;
			BY laannr;
			IF miks_ejd=1 THEN ejendom= 'miks_ejd';
			drop miks_ejd;
		RUN;
		/*Generates the final data set from the merged one*/

		DATA databib.real&aar;
			SET real&aar;
			ejdkom_num = ejdkom * 1; *changes to numeric;
			restobl_andel = restobl * andel; *restobl = finrest (dansk) - some adjustment of payment left (bonds);
			aar = &aar;
			IF restobl GT 0;
			LSTARTAAR = input(substr(laandato, 5,4), 4.);; * Variable for the year the loan was taken;
			AFSLUTAAR = INPUT(SUBSTR(AFSLUT,5,4),4.); *Variable for the year the loan ended;
			IF LSTARTAAR GE 1950; * Keeps only loans taken after 1950;
			
			IF (AFSTART NE 0) AND (AFSLUT EQ 0) THEN afdragsfrihed_nu=1; ELSE afdragsfrihed_nu=0;
			IF RNTBIND NE 30 THEN rentetilpas=1; ELSE rentetilpas=0; *Dummy for interest-adjustment loan (not variable rate);
			IF afdragsfrihed_nu=1 THEN DO;
				AFstartAAR=INPUT(SUBSTR(AFSTART,5,4),4.);
				IF (AFstartAAR GT &aar) OR (AFstartAAR LT 2002) THEN afdragsfrihed_nu=0;
			END;
			afdragsfrihed_for=afdragsfrihed_nu;
			IF AFSLUTAAR EQ &aar THEN afdragsfrihed_for=1;
			IF (afdragsfrihed_nu=1) AND (rentetilpas=1) THEN AF_nu_RT=1; ELSE AF_nu_RT=0;
			IF (afdragsfrihed_for=1) AND (rentetilpas=1) THEN AF_for_RT=1; ELSE AF_for_RT=0;
			IF 0 LE RNTBIND LT 1 THEN F_1=1; ELSE F_1=0;
			IF RNTBIND EQ 1 THEN F1=1; ELSE F1=0;
			IF RNTBIND EQ 3 THEN F3=1; ELSE F3=0;
			IF RNTBIND EQ 5 THEN F5=1; ELSE F5=0;
			IF RNTBIND GE 5 THEN GE_F5=1; ELSE GE_F5=0;

			IF rentetilpas=1 THEN DO;
				IF RNTBIND EQ 0.25 THEN RTtidtilbage=0.25; ELSE RTtidtilbage=min(RNTBIND,RNTBIND-int(RNTBIND*mod((&aar-LSTARTAAR)/RNTBIND,1)));
				IF RNTBIND EQ RTtidtilbage THEN rentetilpas_iaar=1; ELSE rentetilpas_iaar=0;
			END;

			IF 0 LT RTtidtilbage LT 1 THEN RTtid_1=1; ELSE RTtid_1=0;
			IF RTtidtilbage EQ 1 THEN RTtid1=1; ELSE RTtid1=0;
			IF RTtidtilbage EQ 2 THEN RTtid2=1; ELSE RTtid2=0;
			IF RTtidtilbage EQ 3 THEN RTtid3=1; ELSE RTtid3=0;
			IF RTtidtilbage EQ 4 THEN RTtid4=1; ELSE RTtid4=0;
			IF RTtidtilbage EQ 5 THEN RTtid5=1; ELSE RTtid5=0;
			IF RTtidtilbage GT 5 THEN RTtid5_=1; ELSE RTtid5_=0;

			LABEL	andel='Share borrower is liable'
					LSTARTAAR='Year the loan is taken'
					afdragsfrihed_nu='Interest-only per end of the year'
					afdragsfrihed_for='Interest-only during the year'
					rentetilpas='Interest-adjustment loan'
					AF_nu_RT='Interest-only end of year and interest-adjustment loan'
					AF_for_RT='Interest-only during year and interest-adjustment loan'
					F_1='Fixed interest, under 1 year'
					F1='Fixed interest, 1 year'
					F3='Fixed interest, 3 years'
					F5='Fixed interest, 5 years'
					GE_F5='Fixed interest, 5 years or more'
					RTtidtilbage='Time til next interest adjustment'
					rentetilpas_iaar='Interest rate adjusted the prev. year'
					RTtid_1='Less than 1 year until next interest adjustment'
					RTtid1='1 year until next interest adjustment'
					RTtid2='2 years until next interest adjustment'
					RTtid3='3 years until next interest adjustment'
					RTtid4='4 years until next interest adjustment'
					RTtid5='5 years until next interest adjustment'
					RTtid5_='More than 5 years until next interest adjustment'
					;
		RUN;


	%END;
%mend;
%real;

/* Deletes work data sets */
%macro slette_real;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete real1_&aar real&aar Real_miks&aar;
		run;
		quit;
	%end;
%mend;
%slette_real;

/************************************************************************
* 4) The next four macros computes variables of own definition			*
* 	 Fx. are some variables calculated by summation on real estate level*
* 	 Only primary (owner) and secondary residence are included.			*
************************************************************************/

/*The macro samletid generates a data set realkredit&aar, which includes 
only primary and secondary residence and only one observation per loan number*/
%macro samletid;
	%do aar = &startaar_real %to &slutaar_real;
		data realkredit&aar;
		set databib.real&aar;
		IF EJENDOM = "Ejer" OR EJENDOM = "Fritid"; *Restricts to primary (owner/ejer) and secondary (fritid) residence;
		run;

		PROC SORT DATA=realkredit&aar;
			BY laannr laandato;
		RUN;

		DATA realkredit&aar;
			SET realkredit&aar;
			BY laannr;
			IF first.laannr; *Drops duplicates on laannr, only the first person associated with the loan number is included ;
		RUN;
	%END;
%mend;
%samletid;

%macro tid2;
	%DO aar=&startaar_real %TO &slutaar_real;

		/* SUMMATION OVER RELEVANT VARIABLES ON REAL ESTATE LEVEL */
		/* With interest-only */
		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr afdragsfrihed_nu;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr afdragsfrihed_nu;
			VAR RESTKNT RESTOBL LTV;
			OUTPUT OUT=Realkredit_samletAFnu&aar (KEEP= kom_ejdnr RESTKNT RESTOBL LTV afdragsfrihed_nu)
				SUM=RESTKNT RESTOBL LTV;
		RUN;

		DATA Realkredit_samletAFnu&aar;
			SET Realkredit_samletAFnu&aar;
			IF afdragsfrihed_nu=1;
			LABEL 	LTV='Interest-only share of LTV for real estate (end of year)'
					RESTKNT='Interest-only share of RESTKNT for real estate (end of year)'
					RESTOBL='Interest-only share of RESTOBL for real estate (end of year)';
			RENAME 	RESTKNT=AFnu_RESTKNT
					RESTOBL=AFnu_RESTOBL
					LTV=AFnu_LTV;
			DROP afdragsfrihed_nu;
		RUN;

		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr afdragsfrihed_for;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr afdragsfrihed_for;
			VAR RESTKNT RESTOBL LTV;
			OUTPUT OUT=Realkredit_samletAFfor&aar (KEEP= kom_ejdnr RESTKNT RESTOBL LTV afdragsfrihed_for)
				SUM=RESTKNT RESTOBL LTV;
		RUN;

		DATA Realkredit_samletAFrifor&aar;
			SET Realkredit_samletAFfor&aar;
			IF afdragsfrihed_for=1;
			LABEL 	LTV='Interest-only share of LTV for real estate (during the year)'
					RESTKNT='Interest-only share of RESTKNT for real estate (during the year)'
					RESTOBL='Interest-only share of RESTOBL for real estate (during the year)';
			RENAME 	RESTKNT=AFfor_RESTKNT
					RESTOBL=AFfor_RESTOBL
					LTV=AFfor_LTV;
			DROP afdragsfrihed_for;
		RUN;
		DATA Realkredit_samletAfdFor&aar;
			SET Realkredit_samletAFfor&aar;
			IF afdragsfrihed_for=0;
			LABEL 	LTV='Installment share of LTV for the real estate (during the year)'
					RESTKNT='Installment share of RESTKNT for the real estate (during the year)'
					RESTOBL='Installment share of RESTOBL for the real estate (during the year)';
			RENAME 	RESTKNT=AfdFor_RESTKNT
					RESTOBL=AfdFor_RESTOBL
					LTV=AfdFor_LTV;
			DROP afdragsfrihed_for;
		RUN;

		/* With interest adjustment */
		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr rentetilpas;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr rentetilpas;
			VAR RESTKNT RESTOBL LTV;
			OUTPUT OUT=Realkredit_samletRT&aar (KEEP= kom_ejdnr RESTKNT RESTOBL LTV rentetilpas)
				SUM=RESTKNT RESTOBL LTV;
		RUN;

		DATA Realkredit_samletRT&aar;
			SET Realkredit_samletRT&aar;
			IF rentetilpas=1;
			LABEL 	LTV='Share of LTV with interest adjustment for the real estateDel af LTV med rentetilpasning for ejendom'
					RESTKNT='Share of RESTKNT with interest adjustment for the real estate'
					RESTOBL='Share of RESTOBL with interest adjustment for the real estate';
			RENAME 	RESTKNT=RT_RESTKNT
					RESTOBL=RT_RESTOBL
					LTV=RT_LTV;
			DROP rentetilpas;
		RUN;

		DATA RealkreditRTtid&aar;
			SET Realkredit&aar (WHERE= (0 LT RTtidtilbage LE 5));
			RESTKNT_tilp=RESTKNT*RBTYPE/100;
			RESTOBL_tilp=RESTOBL*RBTYPE/100;
		RUN;

		PROC SORT DATA=RealkreditRTtid&aar;
			BY kom_ejdnr RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5;
		RUN;

		PROC MEANS DATA=RealkreditRTtid&aar SUM N NOPRINT;
			BY kom_ejdnr RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5;
			VAR RESTKNT_tilp RESTOBL_tilp;
			OUTPUT OUT=Realkredit_samletRTtid&aar (KEEP= kom_ejdnr RESTKNT_tilp RESTOBL_tilp RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5)
				SUM=RESTKNT_tilp RESTOBL_tilp;
		RUN;

		DATA Realkredit_samletRTtid_1&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid_1=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp_1
					RESTOBL_tilp=RT_RESTOBL_tilp_1;
		RUN;

		DATA Realkredit_samletRTtid1&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid1=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp1
					RESTOBL_tilp=RT_RESTOBL_tilp1;
		RUN;

		DATA Realkredit_samletRTtid2&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid2=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp2
					RESTOBL_tilp=RT_RESTOBL_tilp2;
		RUN;

		DATA Realkredit_samletRTtid3&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid3=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp3
					RESTOBL_tilp=RT_RESTOBL_tilp3;
		RUN;

		DATA Realkredit_samletRTtid4&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid4=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp4
					RESTOBL_tilp=RT_RESTOBL_tilp4;
		RUN;

		DATA Realkredit_samletRTtid5&aar;
			SET Realkredit_samletRTtid&aar;
			IF RTtid5=1;
			RENAME 	RESTKNT_tilp=RT_RESTKNT_tilp5
					RESTOBL_tilp=RT_RESTOBL_tilp5;
		RUN;

		DATA Realkredit_samletRTtid&aar;
			MERGE Realkredit_samletRTtid_1&aar Realkredit_samletRTtid1&aar Realkredit_samletRTtid2&aar 
					Realkredit_samletRTtid3&aar Realkredit_samletRTtid4&aar Realkredit_samletRTtid5&aar;
			BY kom_ejdnr;
			LABEL RT_RESTKNT_tilp_1='Share of RESTKNT interest adjusted within 1 year';
			LABEL RT_RESTOBL_tilp_1='Share of RESTOBL interest adjusted within 1 year';
			LABEL RT_RESTKNT_tilp1='Share of RESTKNT interest adjusted in 1 year';
			LABEL RT_RESTOBL_tilp1='Share of RESTOBL interest adjusted in 1 year';
			LABEL RT_RESTKNT_tilp2='Share of RESTKNT interest adjusted in 2 year';
			LABEL RT_RESTOBL_tilp2='Share of RESTOBL interest adjusted in 2 year';
			LABEL RT_RESTKNT_tilp3='Share of RESTKNT interest adjusted in 3 year';
			LABEL RT_RESTOBL_tilp3='Share of RESTOBL interest adjusted in 3 year';
			LABEL RT_RESTKNT_tilp4='Share of RESTKNT interest adjusted in 4 year';
			LABEL RT_RESTOBL_tilp4='Share of RESTOBL interest adjusted in 4 year';
			LABEL RT_RESTKNT_tilp5='Share of RESTKNT interest adjusted in 5 year';
			LABEL RT_RESTOBL_tilp5='Share of RESTOBL interest adjusted in 5 year';
			DROP RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5;
		RUN;

		/* *With interest-only and interest adjustment*/
		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr AF_nu_RT;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr AF_nu_RT;
			VAR RESTKNT RESTOBL LTV;
			OUTPUT OUT=Realkredit_samletAFRTnu&aar (KEEP= kom_ejdnr RESTKNT RESTOBL LTV AF_nu_RT)
				SUM=RESTKNT RESTOBL LTV;
		RUN;

		DATA Realkredit_samletAFRTnu&aar;
			SET Realkredit_samletAFRTnu&aar;
			IF AF_nu_RT=1;
			LABEL 	LTV='Share of LTV for real estate with intrest-only and interest adjustment (end of year)'
					RESTKNT='Share of RESTKNT for real estate with intrest-only and interest adjustment (end of year)'
					RESTOBL='Share of RESTOBL for real estate with intrest-only and interest adjustment (end of year)';
			RENAME 	RESTKNT=AFRTnu_RESTKNT
					RESTOBL=AFRTnu_RESTOBL
					LTV=AFRTnu_LTV;
			DROP AF_nu_RT;
		RUN;

		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr AF_for_RT;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr AF_for_RT;
			VAR RESTKNT RESTOBL LTV;
			OUTPUT OUT=Realkredit_samletAFRTfor&aar (KEEP= kom_ejdnr RESTKNT RESTOBL LTV AF_for_RT)
				SUM=RESTKNT RESTOBL LTV;
		RUN;

		DATA Realkredit_samletAFRTfor&aar;
			SET Realkredit_samletAFRTfor&aar;
			IF AF_for_RT=1;
			LABEL 	LTV='Share of LTV for real estate with intrest-only and interest adjustment (during the year)'
					RESTKNT='Share of RESTKNT for real estate with intrest-only and interest adjustment (during the year)'
				 	RESTOBL='Share of RESTOBL for real estate with intrest-only and interest adjustment (during the year)';
			RENAME 	RESTKNT=AFRTfor_RESTKNT
					RESTOBL=AFRTfor_RESTOBL
					LTV=AFRTfor_LTV;
			DROP AF_for_RT;
		RUN;

		/* Joint */
		PROC SORT DATA=Realkredit&aar;
			BY kom_ejdnr EJENDOM pnr ;
		RUN;

		PROC MEANS DATA=Realkredit&aar SUM N NOPRINT;
			BY kom_ejdnr EJENDOM;
			VAR RESTKNT RESTOBL LTV afdragsfrihed_nu afdragsfrihed_for rentetilpas AF_nu_RT AF_for_RT F_1 F1 F3 F5 RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5;
			OUTPUT OUT=Realkredit_samletEjd&aar 
				(KEEP= 	kom_ejdnr EJENDOM RESTKNT RESTOBL LTV afdragsfrihed_nu afdragsfrihed_for rentetilpas antal 
						AF_nu_RT AF_for_RT F_1 F1 F3 F5 RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5)
				SUM=RESTKNT RESTOBL LTV afdragsfrihed_nu afdragsfrihed_for rentetilpas AF_nu_RT AF_for_RT F_1 F1 F3 F5 RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5
				N=antal;
		RUN;

		/* Dummies for LTV-level*/
		DATA Realkredit_samletEjd&aar (drop=EJENDOM);
			SET Realkredit_samletEjd&aar;
			IF 0 LE LTV LE 60 THEN LTV_60=1; ELSE LTV_60=0;
			IF 60 LT LTV LE 80 THEN LTV60_80=1; ELSE LTV60_80=0;
			IF 80 LT LTV LE 100 THEN LTV80_100=1; ELSE LTV80_100=0;
			IF LTV GT 100 THEN LTV100_=1; ELSE LTV100_=0;

			IF 60 LT LTV LE 65 THEN LTV60_65=1; ELSE LTV60_65=0;
			IF 65 LT LTV LE 70 THEN LTV65_70=1; ELSE LTV65_70=0;
			IF 70 LT LTV LE 75 THEN LTV70_75=1; ELSE LTV70_75=0;
			IF 75 LT LTV LE 80 THEN LTV75_80=1; ELSE LTV75_80=0;
			IF 80 LT LTV LE 85 THEN LTV80_85=1; ELSE LTV80_85=0;
			IF 85 LT LTV LE 90 THEN LTV85_90=1; ELSE LTV85_90=0;
			IF 90 LT LTV LE 95 THEN LTV90_95=1; ELSE LTV90_95=0;
			IF 95 LT LTV LE 100 THEN LTV95_100=1; ELSE LTV95_100=0;

			/* Equity (Friv�rdi) is computed: 
			  For primary (owner):    (80 pct. of assessed value of property - total mortgage dept in property)
			  For secondary: (60 pct.of assessed value of property - total mortgage dept in property)
			*/
			if LTV ne 0 and EJENDOM="Ejer"   then frivrd_samlet = ((80-LTV)/LTV)*restobl;
			if LTV ne 0 and EJENDOM="Fritid" then frivrd_samlet = ((60-LTV)/LTV)*restobl;

			/* Value of property is calculated */
			if LTV ne 0 then boligvaerdi = 100* restobl/LTV;

			LABEL 	LTV='Total LTV for property'
					frivrd_samlet='Total equity in property'
					LTV_60='LTV under 60'
					LTV60_80='LTV between 60 and 80'
					LTV80_100='LTV between 80 and 100'
					LTV100_='LTV over 100'
					LTV60_65='LTV between 60 and 65'
					LTV65_70='LTV between 65 and 70'
					LTV70_75='LTV between 70 and 75'
					LTV75_80='LTV between 75 and 80'
					LTV80_85='LTV between 80 and 85'
					LTV85_90='LTV between 85 and 90'
					LTV90_95='LTV between 90 and 95'
					LTV95_100='LTV between 95 and 100'
					RESTKNT='Total RESTKNT for property'
					RESTOBL='Total RESTOBL for property'
					afdragsfrihed_nu='Number of interest-only loans(end of year)'
					afdragsfrihed_for='Number of interest-only loans (during the year)'
					rentetilpas='Number of loans with interest adjustment'
					AF_nu_RT='Number of loans with interest-only and interest adjustment (end of year)'
					AF_for_RT='Number of loans with interest-only and interest adjustment (during the year)'
					antal='Number of loans in property'
					F_1='Number of loans in property with a interest adjusment period under 1 year'
					F1='Number of F1 loans in property'
					F3='Number of F3 loans in property'
					F5='Number of F5 loans in property'
					RTtid_1='Number of loans with less than 1 year until interest adjustment'
					RTtid1='Number of loans with 1 year untill interest adjustment'
					RTtid2='Number of loans with 2 year untill interest adjustment' 
					RTtid3='Number of loans with 3 year untill interest adjustment' 
					RTtid4='Number of loans with 4 year untill interest adjustment' 
					RTtid5='Number of loans with 5 year untill interest adjustment';
			RENAME 	LTV=LTV_samlet
					RESTKNT=RESTKNT_samlet
					RESTOBL=RESTOBL_samlet
					afdragsfrihed_nu=afdragsfrihed_nu_samlet
					afdragsfrihed_for=afdragsfrihed_for_samlet
					rentetilpas=rentetilpas_samlet
					AF_nu_RT=AF_nu_RT_samlet
					AF_for_RT=AF_for_RT_samlet
					RTtid_1=RTtid_1_samlet
					RTtid1=RTtid1_samlet
					RTtid2=RTtid2_samlet
					RTtid3=RTtid3_samlet
					RTtid4=RTtid4_samlet
					RTtid5=RTtid5_samlet;
		RUN;

		/*  Merges and saves. Generates variables for the share data set
		Samler og gemmer, samt opretter variabler for andele datas�ttet */
		DATA Realkredit_samletEjd&aar;
			MERGE 	Realkredit_samletEjd&aar 
					Realkredit_samletAFnu&aar 
					Realkredit_samletAFrifor&aar 
					Realkredit_samletAfdFor&aar
					Realkredit_samletRT&aar 
					Realkredit_samletRTtid&aar
					Realkredit_samletAFRTnu&aar 
					Realkredit_samletAFRTfor&aar;
			BY kom_ejdnr;

			IF 0 LE AFnu_LTV LE 60 THEN AFnu_LTV_60=1; ELSE AFnu_LTV_60=0;
			IF 60 LT AFnu_LTV LE 80 THEN AFnu_LTV60_80=1; ELSE AFnu_LTV60_80=0;
			IF 80 LT AFnu_LTV LE 100 THEN AFnu_LTV80_100=1; ELSE AFnu_LTV80_100=0;
			IF AFnu_LTV GT 100 THEN AFnu_LTV100_=1; ELSE AFnu_LTV100_=0;

			IF 0 LE AFfor_LTV LE 60 THEN AFfor_LTV_60=1; ELSE AFfor_LTV_60=0;
			IF 60 LT AFfor_LTV LE 80 THEN AFfor_LTV60_80=1; ELSE AFfor_LTV60_80=0;
			IF 80 LT AFfor_LTV LE 100 THEN AFfor_LTV80_100=1; ELSE AFfor_LTV80_100=0;
			IF AFfor_LTV GT 100 THEN AFfor_LTV100_=1; ELSE AFfor_LTV100_=0;

			IF 0 LE RT_LTV LE 60 THEN RT_LTV_60=1; ELSE RT_LTV_60=0;
			IF 60 LT RT_LTV LE 80 THEN RT_LTV60_80=1; ELSE RT_LTV60_80=0;
			IF 80 LT RT_LTV LE 100 THEN RT_LTV80_100=1; ELSE RT_LTV80_100=0;
			IF RT_LTV GT 100 THEN RT_LTV100_=1; ELSE RT_LTV100_=0;

			IF 0 LE AFRTnu_LTV LE 60 THEN AFRTnu_LTV_60=1; ELSE AFRTnu_LTV_60=0;
			IF 60 LT AFRTnu_LTV LE 80 THEN AFRTnu_LTV60_80=1; ELSE AFRTnu_LTV60_80=0;
			IF 80 LT AFRTnu_LTV LE 100 THEN AFRTnu_LTV80_100=1; ELSE AFRTnu_LTV80_100=0;
			IF AFRTnu_LTV GT 100 THEN AFRTnu_LTV100_=1; ELSE AFRTnu_LTV100_=0;

			IF 0 LE AFRTfor_LTV LE 60 THEN AFRTfor_LTV_60=1; ELSE AFRTfor_LTV_60=0;
			IF 60 LT AFRTfor_LTV LE 80 THEN AFRTfor_LTV60_80=1; ELSE AFRTfor_LTV60_80=0;
			IF 80 LT AFRTfor_LTV LE 100 THEN AFRTfor_LTV80_100=1; ELSE AFRTfor_LTV80_100=0;
			IF AFRTfor_LTV GT 100 THEN AFRTfor_LTV100_=1; ELSE AFRTfor_LTV100_=0;

			/* Afdragsfri */
			RESTKNT_AFnuandel=AFnu_RESTKNT/RESTKNT_samlet;
			RESTOBL_AFnuandel=AFnu_RESTOBL/RESTOBL_samlet;
			LTV_AFnuandel=AFnu_LTV/LTV_samlet;

			RESTKNT_AFforandel=AFfor_RESTKNT/RESTKNT_samlet;
			RESTOBL_AFforandel=AFfor_RESTOBL/RESTOBL_samlet;
			LTV_AFforandel=AFfor_LTV/LTV_samlet;

			/* Afdragsfri og rentetilpasning */
			RESTKNT_AFRTnuandel=AFRTnu_RESTKNT/RESTKNT_samlet;
			RESTOBL_AFRTnuandel=AFRTnu_RESTOBL/RESTOBL_samlet;
			LTV_AFRTnuandel=AFRTnu_LTV/LTV_samlet;

			RESTKNT_AFRTforandel=AFRTfor_RESTKNT/RESTKNT_samlet;
			RESTOBL_AFRTforandel=AFRTfor_RESTOBL/RESTOBL_samlet;
			LTV_AFRTforandel=AFRTfor_LTV/LTV_samlet;

			/* Rentetilpasning */
			RESTKNT_RTandel=RT_RESTKNT/RESTKNT_samlet;
			RESTOBL_RTandel=RT_RESTOBL/RESTOBL_samlet;
			LTV_RTandel=RT_LTV/LTV_samlet;

			/* LABEL */
			LABEL	AFnu_LTV_60='LTV associated with interest-only (end of year) under 60'
					AFnu_LTV60_80='LTV associated with interest-only (end of year) 60-80'
					AFnu_LTV80_100='LTV associated with interest-only (end of year) 80-100'
					AFnu_LTV100_='LTV associated with interest-only (end of year) over 100'
					AFfor_LTV_60='LTV associated with interest-only (during the year) under 60'
					AFfor_LTV60_80='LTV associated with interest-only (during the year) 60-80'
					AFfor_LTV80_100='LTV associated with interest-only (during the year) 80-100'
					AFfor_LTV100_='LTV associated with interest-only (during the year) over 100'
					RT_LTV_60='LTV associated with interest adjustment under 60'
					RT_LTV60_80='LTV associated with interest adjustment 60-80'
					RT_LTV80_100='LTV associated with interest adjustment 80-100'
					RT_LTV100_='LTV associated with interest adjustment over 100'
					AFRTnu_LTV_60='LTV associated with interest-only (end of year) and interest adjustment under 60'
					AFRTnu_LTV60_80='LTV associated with interest-only (end of year) and interest adjustment 60-80'
					AFRTnu_LTV80_100='LTV associated with interest-only (end of year) and interest adjustment 80-100'
					AFRTnu_LTV100_='LTV associated with interest-only (end of year) and interest adjustment over 100'
					AFRTfor_LTV_60='LTV associated with interest-only (during the year) and interest adjustment under 60'
					AFRTfor_LTV60_80='LTV associated with interest-only (during the year) and interest adjustment 60-80'
					AFRTfor_LTV80_100='LTV associated with interest-only (during the year) and interest adjustment 80-100'
					AFRTfor_LTV100_='LTV associated with interest-only (during the year) and interest adjustment over 100'

			/* Interest-only */

					RESTKNT_AFnuandel='Share of RESTKNT with interest-only (end of year)'
					RESTOBL_AFnuandel='Share of RESTOBL with interest-only (end of year)'
					LTV_AFnuandel='Share of LTV with interest-only (end of year)'

					RESTKNT_AFforandel='Share of RESTKNT with interest-only (during the year)'
					RESTOBL_AFforandel='Share of RESTOBL with interest-only (during the year)'
					LTV_AFforandel='Share of LTV with interest-only (during the year)'

			/* Interest-only and interest adjustment*/
					RESTKNT_AFRTnuandel='Share of RESTKNT with interest-only (end of year) and interest adjustment'
					RESTOBL_AFRTnuandel='Share of RESTOBL with interest-only (end of year) and interest adjustment'
					LTV_AFRTnuandel='Share of LTV with interest-only (end of year) and interest adjustment'

					RESTKNT_AFRTforandel='Share of RESTKNT with interest-only (during the year) and interest adjustment'
					RESTOBL_AFRTforandel='Share of RESTOBL with interest-only (during the year) and interest adjustment'
					LTV_AFRTforandel='Share of LTV with interest-only (during the year) and interest adjustment'

			/* Interest adjustment */
					RESTKNT_RTandel='Share of RESTKNT with interest adjustment'
					RESTOBL_RTandel='Share of RESTOBL with interest adjustment'
					LTV_RTandel='Share of LTV with interest adjustment'
		RUN;

		PROC MEANS 
			DATA=Realkredit_samletEjd&aar;
		RUN;

	%END;
%mend;
%tid2;
/* Deletes work data */
%macro slette_real2;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete 	Realkredit_samletAFnu&aar Realkredit_samletAFrifor&aar Realkredit_samletAfdFor&aar Realkredit_samletRT&aar Realkredit_samletRTtid&aar Realkredit_samletAFRTnu&aar 
					Realkredit_samletAFRTfor&aar Realkredit&aar Realkreditrttid&aar Realkredit_samletaffor&aar Realkredit_samletrttid1&aar Realkredit_samletrttid2&aar
					Realkredit_samletrttid3&aar Realkredit_samletrttid4&aar Realkredit_samletrttid5&aar Realkredit_samletrttid_1&aar;
		run;
	%end;
%mend;
%slette_real2;

/*The macro tid3 generates the data set real2_&aar (loan level) where information from the prev. 
generated realkredit_samletejd&aar (work lib) are merged on real&aar data set (from databib)*/
%macro tid3;
	%DO aar=&startaar_real %TO &slutaar_real;

		/* Information is merged on the data set on loan level. A variables indicating in which region the property is located*/

		Proc sort data = databib.real&aar;
			by kom_ejdnr pnr;
		Run;

		Data databib.real2_&aar;
			Merge 	Realkredit_samletEjd&aar
					databib.real&aar (in=a);
			BY kom_ejdnr;
			IF a;
			IF rentetilpas_iaar=. THEN rentetilpas_iaar=0;
			LENGTH landsdel $ 11;
			IF ejdkom IN (101,147,155,185) THEN landsdel='CPH_city';
			ELSE IF ejdkom IN (165,151,153,157,159,161,163,167,169,183,173,175,187) THEN landsdel='CPH_surrounding_area';
			ELSE IF ejdkom IN (201,240,210,250,260,190,270,217,219,223,230) THEN landsdel='Nzealand';
			ELSE IF ejdkom IN (400,411) THEN landsdel='Bornholm';
			ELSE IF ejdkom IN (253,259,350,265,269) THEN landsdel='Ezealand';
			ELSE IF ejdkom IN (320,376,316,326,360,370,306,329,330,340,336,390) THEN landsdel='Vzealand';
			ELSE IF ejdkom IN (420,480,430,440,482,410,450,461,479,492) THEN landsdel='Funen';
			ELSE IF ejdkom IN (530,561,563,607,510,621,540,550,573,575,630,580) THEN landsdel='Sjutland';
			ELSE IF ejdkom IN (710,766,615,707,727,730,741,740,746,706,751) THEN landsdel='Ejutland';
			ELSE IF ejdkom IN (657,661,756,665,760,779,671,791) THEN landsdel='Vjutland';
			ELSE IF ejdkom IN (810,813,860,849,825,846,773,840,787,820,851) THEN landsdel='Njutland';
		RUN;

	%END;
%mend;
%tid3;
/* Delets work data sets */
%macro slette_real3;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete 	Realkredit_samletejd&aar;
		run;
	%end;
%mend;
%slette_real3;

/************************************************************************
* 5) Defines the mortgage data set on individual and loan level.		*
*	 Only primary (owner) and secondary residence are included			*
************************************************************************/
/* First information on mortgage for each loan is merged with information
on the same loan the prev. year for years after 2009*/

%macro real2;
	%do i=&startaar_real %to &slutaar_real;
	%let h = %eval(&i-1);
		proc sort data=databib.Real2_&i out = Real2_&i;
			by laannr pnr;
		run;

		*Data set for 2009 is just equal itself;
		%if &i = 2009 %then %do;
			data Real2_&i;
				set Real2_&i (keep= pnr laannr kom_ejdnr ejendom hovedobl hovedknt restobl restknt andel rbtype fastydl afdragsfrihed_for afdragsfrihed_nu afstartaar 
						afslut laandato lobetid terminer ltidrest rente RBTYPE RLOFT RTtidtilbage bidrag rentetilpas rntbind rentetilpas_iaar 
						RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5 RTtid5_ AF_for_RT AF_nu_RT F1 F3 F5 GE_F5 LSTARTAAR
						LTV_samlet LTV_60 LTV60_80 LTV80_100 LTV100_ restobl_samlet
						AFnu_LTV_60 AFnu_LTV60_80 AFnu_LTV80_100 AFnu_LTV100_ AFfor_LTV_60 AFfor_LTV60_80
						AFfor_LTV80_100 AFfor_LTV100_ RT_LTV_60 RT_LTV60_80 RT_LTV80_100 RT_LTV100_ AFRTnu_LTV_60 AFRTnu_LTV60_80 AFRTnu_LTV80_100
						AFRTnu_LTV100_ rstsep45 rstjn105 frivrd_samlet AFfor_LTV AfdFor_LTV boligvaerdi);
				restobl_1=.;
				restknt_1=.;
				rente_1=.;
				andel_1=.;
			run;
		%end; 

		*The data sets generated for years after 2009 are merged with the prev. year information;
		%if &i > 2009 %then %do; 
		proc sort data=databib.Real2_&h. out = Real2_&h.;
			by laannr pnr;
		run;
			data Real2_&i;
				merge Real2_&i  (in=a keep= pnr laannr kom_ejdnr ejendom hovedobl hovedknt restobl restknt andel rbtype fastydl afdragsfrihed_for afdragsfrihed_nu afstartaar 
						afslut laandato lobetid terminer ltidrest rente RBTYPE RLOFT RTtidtilbage bidrag rentetilpas rntbind rentetilpas_iaar  
						RTtid_1 RTtid1 RTtid2 RTtid3 RTtid4 RTtid5 RTtid5_ AF_for_RT AF_nu_RT F1 F3 F5 GE_F5 LSTARTAAR
						LTV_samlet LTV_60 LTV60_80 LTV80_100 LTV100_ restobl_samlet
						AFnu_LTV_60 AFnu_LTV60_80 AFnu_LTV80_100 AFnu_LTV100_ AFfor_LTV_60 AFfor_LTV60_80
						AFfor_LTV80_100 AFfor_LTV100_ RT_LTV_60 RT_LTV60_80 RT_LTV80_100 RT_LTV100_ AFRTnu_LTV_60 AFRTnu_LTV60_80 
						AFRTnu_LTV80_100 AFRTnu_LTV100_ rstsep45 rstjn105 frivrd_samlet AFfor_LTV AfdFor_LTV boligvaerdi)
					Real2_&h  (keep= laannr pnr restobl restknt hovedknt hovedobl rente andel
						rename=(restobl=restobl_1 restknt=restknt_1 hovedknt=hovedknt_1 hovedobl=hovedobl_1 rente=rente_1 andel=andel_1));
				by laannr pnr;
				if a; *Observations should exists in current year, no condition on existence prev. year;
			run;
		%end;
		
	%end;
%mend;
%real2;


/*	A series of variables are conputed. And a series of variables describing
	the individuals loan are summarized	*/

%macro real3;
	%DO aar=&startaar_real %TO &slutaar_real;
		DATA databib.realkredit_laan&aar (drop = laanaar);
			SET Real2_&aar (where=(EJENDOM="Ejer" OR EJENDOM="Fritid"))
			;
				
			/* Creates a dummy for the type of loan */
			IF rentetilpas=0 THEN 
				fastrente=1; *fixed interest;
			ELSE fastrente=0;
			IF rentetilpas=1 AND afdragsfrihed_for=0 THEN 
				KUN_rentetilpas=1; *ONLY adjustable interest;
			ELSE KUN_rentetilpas=0;
			IF rentetilpas=0 AND afdragsfrihed_for=1 THEN 
				KUN_afdragsfrihed=1; *ONLY interest-only;
			ELSE KUN_afdragsfrihed=0;
			IF rentetilpas=0 AND afdragsfrihed_for=0 THEN 
				KUN_fastrente=1; *ONLY fixed interest;
			ELSE KUN_fastrente=0;
			
			/*Creates a dummy fir arrears (restance)*/
			IF rstjn105 GT 0 THEN 
				restance=1; 
			ELSE IF rstjn105=0 THEN restance=0;

			/*Computes share of the loan with a LTV over 80 */
			if LTV_samlet ne 0 then 
				restobl_80=(max(0,1-(80/LTV_samlet)))*restobl*andel; 
			else if LTV_samlet=0 then restobl_80=0;
			/* Computes share of the loan which is interest-only and has LTV over 60 */
			if AFfor_LTV ne 0 then 
				AF_restobl_60=(max(0,1-(60/AFfor_LTV)))*restobl*andel*afdragsfrihed_for; 
			else if AFfor_LTV=0 then AF_restobl_60=0;
			/* Computes share of the loan with installments and a LTV under 60 assuming that 
			in interest-only loan has 'inderste pant' */
				IF afdragsfrihed_for=0 AND LTV_samlet GT 0 THEN 
				Afd_restobl_60=(min(1,60/LTV_samlet))*restobl*andel; 
			else if AFfor_LTV GE 60 THEN 
				Afd_restobl_60=0;
			else if AFfor_LTV = LTV_samlet THEN 
				Afd_restobl_60=0;
			else if AfdFor_LTV=0 THEN 
				Afd_restobl_60=0;
			else if AFfor_LTV = 0 THEN 
				Afd_restobl_60=(max(0,1-(60/LTV_samlet)))*restobl*andel; 
			else if AFfor_LTV LT 60 AND AFfor_LTV LT LTV_samlet THEN 
				Afd_restobl_60=(max(0,((60-AFfor_LTV)/100)))*restobl*andel; 

			/* Calculations above - for new lendings (nyudl�n) only */
			IF LSTARTAAR EQ &aar THEN DO;
				/* Computes share of loan with LTV over 80 */
				if LTV_samlet ne 0 THEN 
					restobl_80_nylaan=(max(0,1-(80/LTV_samlet)))*restobl*andel; 
				else if LTV_samlet=0 THEN 
					restobl_80_nylaan=0;
				/* Computes share of loan with interest-only and a LTV over 60*/
				if AFfor_LTV ne 0 THEN 
					AF_restobl_60_nylaan=(max(0,1-(60/AFfor_LTV)))*restobl*andel*afdragsfrihed_for; 
				else if AFfor_LTV=0 THEN 
					AF_restobl_60_nylaan=0;

				/* Computes share of the loan with installments and a LTV under 60 assuming that 
				in interest-only loan has 'indereste pant' */
				if AFfor_LTV GE 60 then Afd_restobl_60_nylaan=0;
				else if AFfor_LTV = LTV_samlet THEN 
					Afd_restobl_60_nylaan=0;
				else if AfdFor_LTV=0 THEN 
					Afd_restobl_60_nylaan=0;
				else if AFfor_LTV LT 60 AND AFfor_LTV LT LTV_samlet THEN 
					Afd_restobl_60_nylaan=(max(0,((60-AFfor_LTV)/100)))*restobl*andel*(1-afdragsfrihed_for); 
			END;

			/* computes the size of the loan when accounted for the share the borrower is liable          */
			/* for both bond value (obligationsv�rdi) and cash value (kontantv�rdi) for the principal (hovedstol) and outstanding dept (restg�ld)  */

			hovedobl_andel	= hovedobl*andel;
			hovedknt_andel	= hovedknt*andel;
			restobl_andel	= restobl*andel;
			restknt_andel	= restknt*andel;
			restknt_andel_1	= restknt_1*andel_1;

			/* Computes the share of BIDRAG the borrower is liable */
			bidrag_andel	= Bidrag*andel;


			/* Computes the share of the principal (hovedstol) and outstanding dept (restg�ld) with interest adjustment */
			restobl_andel_rtl = restobl_andel*rbtype/100;
			restobl_80_rtl = restobl_80*rbtype/100;

			/* Computes share of the loan, where the interest is adjusted within respectively under 1 year, 1 year, 2 years etc.  */
			    
			/*Interest adjusted in less than 1 year*/
				restobl_andel_rtl_tilp_1 = restobl_andel_rtl*RTtid_1;
			   
			/*Interest adjusted in 1 year*/
					restobl_andel_rtl_tilp1 = restobl_andel_rtl*RTtid1;
			    
			/*Interest adjusted in 2 years*/
				restobl_andel_rtl_tilp2 = restobl_andel_rtl*RTtid2;

			/*Interest adjusted in 3 years*/
					restobl_andel_rtl_tilp3 = restobl_andel_rtl*RTtid3;
			    
			/*Interest adjusted in 4 years*/
					restobl_andel_rtl_tilp4 = restobl_andel_rtl*RTtid4;

			/*Interest adjusted in 5 years*/
					restobl_andel_rtl_tilp5 = restobl_andel_rtl*RTtid5;

			/* Computes a dummy for if the loan was taken between 2003 and 2010 */

			length optag0310 optag0210 3.;
			if substr(laandato,5,4) ge 2003 and substr(laandato,5,4) le 2010 then 
				optag0310=1; 
			else optag0310=0;
			if substr(laandato,5,4) ge 2002 and substr(laandato,5,4) le 2010 then 
				optag0210=1; 
			else optag0210=0;

			/* Creates variables for year and month loan was taken */
			optag_aar = substr(laandato,5,4)*1;
			optag_maaned = substr(laandato,3,2)*1;
			optag_aar_maaned = optag_aar*100+optag_maaned;
			optag_aar_restance = optag_aar * restance;

			/* Year loan was taken, primary residence (owner) only*/
			if ejendom='Ejer' then do;
				optag_aar_ejer=optag_aar; 
				optag_aar_res_ejer=optag_aar_restance;
			end;
			else do;
				optag_aar_ejer=.;
				optag_aar_res_ejer=.;
			end;

			/* Dummies for the year the loan was taken, and year loan was taken and interest-only*/
			laanaar = input(substr(laandato,5,4), 4.);
			length  optag&startaar-optag&slutaar_real
					optag_rentilp&startaar-optag_rentilp&slutaar_real
					optag_afdfri2003-optag_afdfri&slutaar_real
					optag_AfdRen2003-optag_AfdRen&slutaar_real 3.;

			%do i=&startaar %to &slutaar_real;
				if laanaar = &i then 
					optag&i=1; 
				else optag&i=0; 
				optag_rentilp&i = optag&i*rentetilpas;
				%if &i >= 2003 %then %do;
					optag_afdfri&i = optag&i*afdragsfrihed_for;
					optag_AfdRen&i = optag&i*AF_for_RT;
				%end;
			%end;

			/* Shares (of the loans) multiplied by an indicator for interest-only - done to be able to summarize over loans with interest-only  */

				restobl_andel_afdfri  = restobl_andel*afdragsfrihed_for;
				restobl_80_afdfri=restobl_80 *afdragsfrihed_for;

			/* Computes the share which is both interest-only and with interest adustment*/
				restobl_andel_rtlAfdfri = restobl_andel*afdragsfrihed_for*rbtype/100;
				restobl_80_rtlAfdfri = restobl_80*afdragsfrihed_for*rbtype/100;

			/* Outstanding dept multiplied with share and indicator for differente types of interests */
				restobl_FastRente=restobl_andel*fastrente; *fixed interest;
				restobl_VarRente=restobl_andel*rentetilpas; * interest adjustment ;

				restobl_f5=restobl_andel*F5; *F5;
				restobl_ge_f5=restobl_andel*ge_F5;

				restobl_80_FastRente=restobl_80*fastrente; *Fixed interest;
				restobl_80_VarRente=restobl_80*rentetilpas; *interest adjustment;

			/* Indicators for end of interest-only period */
			/* Interaction betweem end of interest-only period and current LTV in the property where the loan was recorded */ 
			/* A help-variable is computed, it is later used to compute outstanding dept for the property with the highest LTV amoungst the properties 
			   the individual has mortgaged, where the interestonly period ends a specific year.
			   The variable is computed to calculate the outstanding dept in the property with the highest LTV and not the highest outstanding dept, 
			   as this does not necessarily coincide */ 
	
				length	udloeb2013-udloeb&AFUdloebslut
					udloeb_LTV2013-udloeb_LTV&AFUdloebslut
					udloeb_hjaelp2013-udloeb_hjaelp&AFUdloebslut 3.;
			%do i=2003 %to &slutaar_real;
				%let j = %eval(&i + 10);
				if afstartaar = &i and afslut=0 then 
					udloeb&j=1; 
				else udloeb&j=0; 
				if udloeb&j=1 then do; 
					udloeb_LTV&j=LTV_samlet;  
					udloeb_hjaelp&&j = 10000*LTV_samlet+restobl_samlet/1000000; 
				end;
			%end;


			/************************************************************************************************************************************************************/
			/*Calculation of installments the past year                                                                                                                     */
			/************************************************************************************************************************************************************/

			/* First some variables to help the calculation; number of terms/payments (terminer?) in the entire duration of the loan.
			   The number of the first and the last installment/payment in the current year, and the remaining payments after the current year. */

			/* Number of payments in the duration of the loan*/
			term_ialt   = lobetid*terminer;

			/* Number of payments in the first year of the duration of the loan (variable term_aar1 is generated)*/
			if substr(laandato,3,2)='01' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=4; if terminer=12 then term_aar1=12; 
			end;
			if substr(laandato,3,2)='02' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=4; if terminer=12 then term_aar1=11; 
			end;
			if substr(laandato,3,2)='03' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=4; if terminer=12 then term_aar1=10; 
			end;
			if substr(laandato,3,2)='04' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=3; if terminer=12 then term_aar1=9; 
			end;
			if substr(laandato,3,2)='05' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=3; if terminer=12 then term_aar1=8; 
			end;
			if substr(laandato,3,2)='06' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=2; if terminer=4 then term_aar1=3; if terminer=12 then term_aar1=7; 
			end;
			if substr(laandato,3,2)='07' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=2; if terminer=12 then term_aar1=6; 
			end;
			if substr(laandato,3,2)='08' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=2; if terminer=12 then term_aar1=5; 
			end;
			if substr(laandato,3,2)='09' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=2; if terminer=12 then term_aar1=4; 
			end;
			if substr(laandato,3,2)='10' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=1; if terminer=12 then term_aar1=3; 
			end;
			if substr(laandato,3,2)='11' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=1; if terminer=12 then term_aar1=2; 
			end;
			if substr(laandato,3,2)='12' then do; 
				if terminer=1 then term_aar1=1; if terminer=2 then term_aar1=1; if terminer=4 then term_aar1=1; if terminer=12 then term_aar1=1; 
			end;

			/*Number of payments made by the start of the current year*/

			term_betalt_pri = max((&aar-substr(laandato,5,4)-1)*terminer+term_aar1,0); 

			/*Number of payments made by the end of the current year*/
			term_betalt_ult = max((&aar-substr(laandato,5,4))*terminer+term_aar1,0); 

			/*Number of payments left by the end of the current year */
			term_rest1 = max(term_ialt-term_betalt_ult,1); *Based on original duration;
			term_rest2 = ltidrest*terminer;                 *Based on the informed remaining duration of the loan;

			*If the two variables above differ a lot (term_rest2>term_rest1+terminer), term_rest2 is used. If not, then term_rest1 is used, since it is more accurate;
			if term_rest2 > term_rest1 + terminer then 
				term_rest = term_rest2; 
			else term_rest = term_rest1;  

			/*The numbers of the first and last payment in the current year*/
			term_start  = min(term_betalt_pri+1,term_ialt);
			term_slut   = min(term_betalt_ult,term_ialt);


			rente100    = rente/100;
			rente100_1  = rente_1/100;
			term_rest_1 = min(term_rest+terminer,term_ialt);

			/*1st method: compute installment payment the current year as difference between remaining dept at the end
			of the year and the remaining dept the end of the prev. year.
			Only works for 2010 and 2011*/
			afdrag1=restobl_andel_1-restobl_andel;

			/* 2nd method: compute intallment payment the current year from the prinipal (hovedstol), the original duration and the current interest rate. 
						   Should work for loans with fixed interest rate without interest-only periods, but does not necessarily works for other types of loans*/
			if term_ialt gt 0 and term_start gt 0 and term_slut gt 0 and term_rest ge 0 and rente100 gt 0 and hovedknt gt 0 then 
				afdrag2= (-1)*FINANCE('CUMPRINC',rente100/terminer,term_ialt,hovedknt,term_start,term_slut,0)*andel;


			/* 3rd method: compute installment payment from information on the outstanding dept, remaining payments by the start of the current year and the current 
						   interest rate. 
						   The challenge is that the outstanding dept by the start of the year is unknown, which is key. In stead, the outstanding dept by the 
						   end of the year is used and the remaining duration is set equal the number of payments to be made by the end of year.
						   Thus, the computed size is the installment that should be paid in the following year - in many cases this will be close to the installment
						   paid the current year.
						   For loans taken the current year is the remaining dept by the start of the year set equal to the principal.   

			 */

			if substr(laandato,5,4) lt &aar and rente100 gt 0 and restobl gt 0 then 
				afdrag3=(-1)*FINANCE('CUMPRINC',rente100/terminer,max(round(term_rest2,1),terminer),restobl_andel,1,terminer,0);
			if substr(laandato,5,4) eq &aar and rente100 gt 0 and hovedknt gt 0 then 
				afdrag3=(-1)*FINANCE('CUMPRINC',rente100/terminer,term_ialt,hovedknt,1,term_aar1,0)*andel;


			/* 4th method: as the 3rd method, but with an exta iteration: the remaining dept by th start of the year is approximated by the remaining dept 
						   at the end of the year plus the payments estimated by the 3rd method.
						   Next, the payments are recalculated by the approximated remaining dept by the end of the year and the number of remaining payments
						   by the start of the year.
						   For loans taken the current year is the remaining dept by the start of the year set equal to the principal.

			 */
			if substr(laandato,5,4) lt &aar and rente100 gt 0 and restobl_andel gt 0 and afdrag3 gt 0 then 
				afdrag4=(-1)*FINANCE('CUMPRINC',rente100/terminer,term_rest+terminer,restobl_andel+afdrag3,1,terminer,0); 
			if substr(laandato,5,4) eq &aar and rente100 gt 0 and hovedknt gt 0 then 
				afdrag4=(-1)*FINANCE('CUMPRINC',rente100/terminer,term_ialt,hovedknt,1,term_aar1,0)*andel;

			/* 5th method: compute a yearly payment from the original duration and the principal, fine the installment (afdrag) by subtracting the interest payments */
			if hovedknt gt 0 and rente100 gt 0 and terminer gt 0 and lobetid gt 0 then do;
				ydelse5=((HOVEDKNT*rente100/terminer)/(1-(1+rente100/terminer)**(-Lobetid*terminer)))*terminer*andel;	/* Payment (Ydelse) calulated from original principal and duration	*/
				afdrag5=ydelse5-restobl_andel*((1+rente100/terminer)**(terminer)-1);
			end;

			/* 6th method: calculate the yearly total payment (ydelse) from the remaining dept and durtion first, then compute installment payment (afdrag)*/
			if restobl gt 0 and rente100 gt 0 and terminer gt 0 and ltidrest gt 0 then do;
				ydelse6=((restobl*rente100/terminer)/(1-(1+rente100/terminer)**(-ltidrest*terminer)))*terminer*andel;			/* Total payment calculated from the remaning dept and duration   */
				afdrag6=ydelse6-restobl_andel*((1+rente100/terminer)**(terminer)-1);
			end;

			/* 7th method: as the 5th method, but without accounting for the number of payments (terminer). */
			if hovedknt gt 0 and rente100 gt 0 and lobetid gt 0 then do;
				ydelse7=((HOVEDKNT*rente100)/(1-(1+rente100)**(-Lobetid)))*andel;
				afdrag7=ydelse7-restobl_andel*rente100;
			end;

			/*8th method: as the 6th method, but without accounting for the number of payments (terminer) */
			if restobl gt 0 and rente100 gt 0 and ltidrest gt 0 then do;
				ydelse8=((restobl*rente100)/(1-(1+rente100)**(-ltidrest)))*andel;
				afdrag8=ydelse8-restobl_andel*rente100;
			end;

			/*Control: calculates the devaition (percent) between the different methods employed*/

			if afdrag1 ne 0 then afvig12 = (afdrag2-afdrag1)/afdrag1*100;
			if afdrag1 ne 0 then afvig13 = (afdrag3-afdrag1)/afdrag1*100; 
			if afdrag1 ne 0 then afvig14 = (afdrag4-afdrag1)/afdrag1*100;
			if afdrag1 ne 0 then afvig15 = (afdrag5-afdrag1)/afdrag1*100;
			if afdrag1 ne 0 then afvig16 = (afdrag6-afdrag1)/afdrag1*100;
			if afdrag1 ne 0 then afvig17 = (afdrag7-afdrag1)/afdrag1*100;
			if afdrag1 ne 0 then afvig18 = (afdrag8-afdrag1)/afdrag1*100;


			/* Final calculation of payment (afdrag). If possible, the 1st method is employed. If not, then 
				- fixed interest rate without a interest-only period: 2nd method
				- loans with interest adjustment without a interest-only period: 4th method
				- interest-only: payment (afdrag) is 0 

			*/


			if afdrag1 gt 0 then do; 
				afdrag_for=afdrag1; 
				afd_metode=1; 
			end;
			else do;
				if afdragsfrihed_for=0 and rbtype  = 0 then do; 
					afdrag_for=afdrag2; 
					afd_metode=2; 
				end;
				if afdragsfrihed_for=0 and rbtype gt 0 then do; 
					afdrag_for=afdrag4; 
					afd_metode=4; 
				end; 
				if afdragsfrihed_for=1 then do; 
					afdrag_for=0; 
					afd_metode=0; 
				end; 
			end;
			if afdrag_for=. then afd_metode=.;


			/* Calculation of payment (afdrag) in the following year: calculated from the current remaining dept, interest and duration.
			   For loan with interest-only be the end of the year, the payment (afdrag) is 0.  
		 */

			 if afdragsfrihed_nu=0 and rente100 gt 0 and restobl gt 0 
			                       then afdrag_nu = (-1)*FINANCE('CUMPRINC',rente100/terminer,term_rest,restobl,1,min(terminer,term_rest),0)*andel;
			 if afdragsfrihed_nu=1 then afdrag_nu = 0;


			/* Calculation of interest rate payment in the current and following year */

			/* Current year: calculated from the current interest rate and the remaining dept by the start of the year. The latter is approximated by the 
			   remaining dept by the end of the year plus the former calculated payment (afdrag9 in the current year*/
			 
			 *First loan taken prior to the current year;
			if substr(laandato,5,4) lt &aar then do; 
				if afdragsfrihed_for=0 and rente100 gt 0 and restobl ge 0 and afdrag_for ge 0 then 
				rente_bet_for = (-1)*FINANCE('CUMIPMT',rente100/terminer,term_rest+terminer,restobl_andel+afdrag_for,1,terminer,0);
				if afdragsfrihed_for=1 then rente_bet_for = restobl_andel*rente100;

				if afdragsfrihed_for=0 and rente100_1 gt 0 and restobl_1 gt 0 then
				rente_bet_for2 = (-1)*FINANCE('CUMIPMT',rente100_1/terminer,term_rest+terminer,restobl_1,1,terminer,0)*andel;
				if afdragsfrihed_for=1 then rente_bet_for2 = restobl_1*andel*rente100_1;
			end;

			*Then, loans taken in the current year. The remaining dept is equal the principal.;
			if substr(laandato,5,4) eq &aar then do; 
				if afdragsfrihed_for=0 and rente100 gt 0 and hovedknt gt 0 then 
				rente_bet_for = (-1)*FINANCE('CUMIPMT',rente100/terminer,term_ialt,hovedknt,1,term_aar1,0)*andel;
				if afdragsfrihed_for=1 then rente_bet_for = hovedknt_andel*rente100*(term_aar1/terminer);

				rente_bet_for2=rente_bet_for;
			end;

			rente_simpel = restobl_andel*rente100;
			if rente_bet_for eq . and rente_bet_for2 gt 0 then rente_bet_for=rente_bet_for2;
			if rente_bet_for eq . and rente_bet_for2 eq . and rente_simpel gt 0 then rente_bet_for = rente_simpel;

			 /*The following year: calculated from the current interest rate and the remaining dept by the end of the year*/

			 if afdragsfrihed_nu=0 and rente100 gt 0 and restobl gt 0 then 
			    rente_bet_nu = (-1)*FINANCE('CUMIPMT',rente100/terminer,term_rest,restobl,1,min(terminer,term_rest),0)*andel; 
			 if afdragsfrihed_nu=1 then rente_bet_nu = restobl_andel*rente100;

			 rente_bet_nu_fast = rente_bet_nu * fastrente;
			 rente_bet_nu_variabel = rente_bet_nu - rente_bet_nu_fast;

			/* Calculation of a hypothetical payment (afdrag) and interest rate payment for interest-only loans:
			 	equal to the payment (afdrag) the borrower would have to make, if the loan should be paid back over the next 20 years (or the actual duration of the loan, if
			 	this is less than 20 years) and the current interest rate. */

			if afdragsfrihed_nu=1 then do;
				if rente100 gt 0 and restobl gt 0 THEN DO;
					afdrag_hyp = (-1)*FINANCE('CUMPRINC',rente100/terminer,min(terminer*20,term_rest),restobl,1,min(terminer,term_rest),0)*andel;
					rente_afd_hyp = (-1)*FINANCE('CUMIPMT',rente100/terminer,min(terminer*20,term_rest),restobl,1,min(terminer,term_rest),0)*andel; 
				end;
			end;

			IF rente_afd_hyp GT 0 THEN rente_afd_hyp_ret=rente_afd_hyp; ELSE rente_afd_hyp_ret=rente_bet_nu;

			/* Changes format of dummies to save space */

			length rentetilpas    AF_for_RT        AF_nu_RT F1       F3
				   F5             GE_f5			   optag0310  	   RTtid_1		     RTtid1		     RTtid2
				   RTtid3		  RTtid4		   RTtid5			 RTtid5_
				   LTV_60		  LTV60_80         LTV80_100         LTV100_		   
			       AFnu_LTV_60	  AFnu_LTV60_80	   AFnu_LTV80_100    AFnu_LTV100_
				   AFfor_LTV_60	  AFfor_LTV60_80   AFfor_LTV80_100   AFfor_LTV100_
				   RT_LTV_60	  RT_LTV60_80	   RT_LTV80_100 	 RT_LTV100_
				   AFRTnu_LTV_60  AFRTnu_LTV60_80  AFRTnu_LTV80_100  AFRTnu_LTV100_
				   fastrente 	  KUN_rentetilpas  KUN_afdragsfrihed KUN_fastrente
			    3.;  

		RUN;

		PROC SORT DATA=databib.realkredit_laan&aar;
			BY pnr kom_ejdnr;
		RUN;

	%END;
%mend;
%real3;		/* Runs the macro */
/* Delets work data sets */
%macro slette_real4;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete 	Real2_&aar;
		run;
	%end;
%mend;
%slette_real4;


	/*The macro real4 adds information on the individual's residence (in order to determine the LTV and equity (friv�rdi) for the primary residence, that is the 
		 residence where the individual lives)*/
%macro real4;
	%DO aar=&startaar_real %TO &slutaar_real;
		data databib.realkredit_laan&aar;
			merge 	databib.realkredit_laan&aar (in=a)
					bopikom_ejdnr_&aar (keep=pnr kom_ejdnr rename=(kom_ejdnr=kom_ejdnr_pribol));
			by pnr;
			if a; *uses all (and only) observations from databib.realkredit_laan&aar;;
			if kom_ejdnr=kom_ejdnr_pribol then do;
				LTV_pribol = LTV_samlet;
				optag_aar_pribol = optag_aar;
			end;

		run;

	%END;
%mend;
%real4;		/* Runs macro */

/* Deletes work data sets */
%macro slette_real5;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete 	Bopikom_ejdnr_&aar;
		run;
	%end;
%mend;
%slette_real5;


/*The macro real5 generates the data set databib.rk_Individdata&aar by summarizing over the variables on pnr, loan, and property level*/
%macro real5;
	%DO aar=&startaar_real %TO &slutaar_real;

		/*Summation over variables on individual level*/
		proc means data = databib.realkredit_laan&aar noprint;
				var	hovedobl_andel				hovedknt_andel				restobl_andel				restknt_andel
				restobl_andel_rtl			
				restobl_andel_afdfri		
				restobl_andel_rtlAfdfri		
				restobl_andel_rtl_tilp_1	
				restobl_andel_rtl_tilp1		
				restobl_andel_rtl_tilp2
				restobl_andel_rtl_tilp3
				restobl_andel_rtl_tilp4
				restobl_andel_rtl_tilp5
				AF_restobl_60					AF_restobl_60_nylaan		Afd_restobl_60				Afd_restobl_60_nylaan
				restobl_80						restobl_80_rtl				restobl_80_afdfri			restobl_80_rtlAfdfri
				restobl_FastRente				restobl_VarRente			restobl_F5				restobl_GE_F5
				restobl_80_FastRente			restobl_80_VarRente
				afdragsfrihed_for				afdragsfrihed_nu
				rentetilpas						fastrente					KUN_rentetilpas				KUN_afdragsfrihed 
				KUN_fastrente					rentetilpas_iaar
				AF_for_RT						AF_nu_RT                    
				F1								F3							F5							ge_f5
				optag0310						optag0210
				RTtid_1							RTtid1						RTtid2
				RTtid3							RTtid4						RTtid5						RTtid5_
				LTV_60							LTV60_80					LTV80_100					LTV100_
				AFnu_LTV_60						AFnu_LTV60_80				AFnu_LTV80_100				AFnu_LTV100_
				AFfor_LTV_60					AFfor_LTV60_80				AFfor_LTV80_100				AFfor_LTV100_
				RT_LTV_60						RT_LTV60_80					RT_LTV80_100				RT_LTV100_
				AFRTnu_LTV_60					AFRTnu_LTV60_80				AFRTnu_LTV80_100			AFRTnu_LTV100_
				afdrag_for						afdrag_nu					afdrag_hyp  				 rente_afd_hyp                 
				rente_bet_for					rente_bet_nu				rente_afd_hyp_ret
				bidrag_andel					rente_bet_nu_fast			rente_bet_nu_variabel
				optag_aar						optag_aar_maaned			optag_aar_pribol			optag_aar_ejer
				optag_aar_restance				optag_aar_res_ejer
				rstsep45						rstjn105
				LTV_samlet						LTV_pribol					AFfor_LTV
				udloeb2013-udloeb&AFUdloebslut
				udloeb_LTV2013-udloeb_LTV&AFUdloebslut
				udloeb_hjaelp2013-udloeb_hjaelp&AFUdloebslut
				optag&startaar - optag&slutaar_real
				optag_rentilp&startaar - optag_rentilp&slutaar_real
				optag_afdfri2003 - optag_afdfri&slutaar_real
				optag_AfdRen2003 - optag_AfdRen&slutaar_real;
			by pnr;

			output out=databib.rk_Individdata&aar 

			sum( hovedobl_andel					hovedknt_andel				restobl_andel				restknt_andel	
				restobl_andel_rtl			
				restobl_andel_afdfri		
				restobl_andel_rtlAfdfri 	
				restobl_andel_rtl_tilp_1 	
				restobl_andel_rtl_tilp1  	
				restobl_andel_rtl_tilp2  
				restobl_andel_rtl_tilp3  
				restobl_andel_rtl_tilp4  
				restobl_andel_rtl_tilp5
				AF_restobl_60					AF_restobl_60_nylaan		Afd_restobl_60				Afd_restobl_60_nylaan
				restobl_80						restobl_80_rtl				restobl_80_afdfri			restobl_80_rtlAfdfri
				restobl_FastRente				restobl_VarRente			restobl_F5					restobl_GE_F5
				restobl_80_FastRente		restobl_80_VarRente
				afdragsfrihed_for				afdragsfrihed_nu                   
				rentetilpas						fastrente					KUN_rentetilpas				KUN_afdragsfrihed 
				KUN_fastrente					rentetilpas_iaar
				AF_for_RT						AF_nu_RT                    
				F1								F3							F5
				optag0310						optag0210
				RTtid_1							RTtid1						RTtid2
				RTtid3							RTtid4						RTtid5						RTtid5_
				LTV_60							LTV60_80					LTV80_100					LTV100_
				AFnu_LTV_60						AFnu_LTV60_80				AFnu_LTV80_100				AFnu_LTV100_
				AFfor_LTV_60					AFfor_LTV60_80				AFfor_LTV80_100				AFfor_LTV100_
				RT_LTV_60						RT_LTV60_80					RT_LTV80_100				RT_LTV100_
				AFRTnu_LTV_60					AFRTnu_LTV60_80				AFRTnu_LTV80_100			AFRTnu_LTV100_
				afdrag_for						afdrag_nu					afdrag_hyp					rente_afd_hyp               
				rente_bet_for					rente_bet_nu				rente_afd_hyp_ret
				bidrag_andel					rente_bet_nu_fast			rente_bet_nu_variabel
				rstsep45						rstjn105
				udloeb2013 - udloeb&AFUdloebslut
				optag&startaar - optag&slutaar_real
				optag_rentilp&startaar - optag_rentilp&slutaar_real
				optag_afdfri2003 - optag_afdfri&slutaar_real
				optag_AfdRen2003 - optag_AfdRen&slutaar_real)
			= 	hovedobl						hovedknt					restobl						restknt
				restobl_rtl
				restobl_afdfri
				restobl_rtlafdfri
				restobl_rtl_tilp_1
				restobl_rtl_tilp1
				restobl_rtl_tilp2
				restobl_rtl_tilp3
				restobl_rtl_tilp4
				restobl_rtl_tilp5
				AF_restobl_60					AF_restobl_60_nylaan		Afd_restobl_60				Afd_restobl_60_nylaan
				restobl_80						restobl_80_rtl				restobl_80_afdfri			restobl_80_rtlAfdfri
				restobl_FastRente				restobl_VarRente			restobl_F5					restobl_GE_F5	
				restobl_80_FastRente		restobl_80_VarRente
				afdragsfrihed_for				afdragsfrihed_nu
				rentetilpas						fastrente					KUN_rentetilpas				KUN_afdragsfrihed 
				KUN_fastrente					rentetilpas_iaar
				AF_for_RT						AF_nu_RT
				F1								F3							F5								
				optag0310						optag0210
				RTtid_1							RTtid1						RTtid2
				RTtid3							RTtid4						RTtid5						RTtid5_
				LTV_60							LTV60_80					LTV80_100					LTV100_
				AFnu_LTV_60						AFnu_LTV60_80				AFnu_LTV80_100				AFnu_LTV100_
				AFfor_LTV_60					AFfor_LTV60_80				AFfor_LTV80_100				AFfor_LTV100_
				RT_LTV_60						RT_LTV60_80					RT_LTV80_100				RT_LTV100_
				AFRTnu_LTV_60					AFRTnu_LTV60_80				AFRTnu_LTV80_100			AFRTnu_LTV100_
				afdrag_for						afdrag_nu					afdrag_hyp					rente_afd_hyp
				rente_bet_for					rente_bet_nu				rente_afd_hyp_ret
				bidrag							rente_bet_nu_fast			rente_bet_nu_variabel
				rstsep45						rstjn105
				udloeb2013 - udloeb&AFUdloebslut
				optag&startaar - optag&slutaar_real
				optag_rentilp&startaar - optag_rentilp&slutaar_real
				optag_afdfri2003 - optag_afdfri&slutaar_real
				optag_AfdRen2003 - optag_AfdRen&slutaar_real

			n(afdrag_for afdrag_nu rente_bet_for rente_bet_nu) 
			= afdrag_for_antal afdrag_nu_antal rente_bet_for_antal rente_bet_nu_antal

			max(LTV_samlet						LTV_pribol					AFfor_LTV
				optag_aar						optag_aar_maaned 			optag_aar_pribol			optag_aar_ejer
				optag_aar_restance				optag_aar_res_ejer
				udloeb_LTV2013 - udloeb_LTV&AFUdloebslut
				udloeb_hjaelp2013 - udloeb_hjaelp&AFUdloebslut)
			= 	maxLTV							LTV_pribol					AFfor_maxLTV
				senest_optag					senest_optag_aar_maaned 	senest_optag_pribol			senest_optag_ejer
				senest_optag_restance			senest_optag_res_ejer
				udloeb_maxLTV2013 - udloeb_maxLTV&AFUdloebslut
				udloeb_hjaelp2013 - udloeb_hjaelp&AFUdloebslut

			min(optag_aar optag_aar_pribol optag_aar_ejer) 
			= tidligst_optag tidligst_optag_pribol tidligst_optag_ejer / keeplen;

		run;

		/*Constructs a data set with variables that should be summarized over the individual's properties (not loans)*/
		proc means data=databib.realkredit_laan&aar (keep=pnr kom_ejdnr restobl_andel) noprint;
			var restobl_andel;
			by pnr kom_ejdnr;

			output out=restobl_ejd&aar

			sum(restobl_andel)=restobl_andel_ejd;
		run;


		data realkredit_ejd&aar;
			merge databib.realkredit_laan&aar (keep=pnr kom_ejdnr kom_ejdnr_pribol ejendom frivrd_samlet boligvaerdi restobl_samlet)
			      restobl_ejd&aar             (keep=pnr kom_ejdnr restobl_andel_ejd);
			by pnr kom_ejdnr;
			if first.kom_ejdnr;
			frivrd_andel=frivrd_samlet*restobl_andel_ejd/restobl_samlet;
			boligvaerdi_andel= boligvaerdi*restobl_andel_ejd/restobl_samlet;

			if kom_ejdnr eq kom_ejdnr_pribol then frivrd_pribol=frivrd_andel; else frivrd_pribol=.;
			if kom_ejdnr eq kom_ejdnr_pribol then boligvaerdi_pribol=boligvaerdi_andel; else boligvaerdi_pribol=.;
			if ejendom='Ejer'                then frivrd_ejer  =frivrd_andel; else frivrd_ejer  =0;  
			if ejendom='Fritid'              then frivrd_fritid=frivrd_andel; else frivrd_fritid=0;  
		run;

		proc means data=realkredit_ejd&aar noprint;
			var frivrd_andel frivrd_pribol frivrd_ejer frivrd_fritid boligvaerdi_pribol;
			by pnr;
			output out=individdata_ejd&aar
			sum(frivrd_andel frivrd_pribol frivrd_ejer frivrd_fritid boligvaerdi_pribol boligvaerdi_andel)
			= frivrd_alle  frivrd_pribol frivrd_ejer frivrd_fritid boligvaerdi_pribol boligvaerdi_real / keeplen;
		run;

		/* Combines the information summarized on loan with the information on property and construsts additional variables*/

		data databib.rk_Individdata&aar (drop= afdrag_for_antal afdrag_nu_antal rente_bet_for_antal rente_bet_nu_antal udloeb_hjaelp2013-udloeb_hjaelp&AFUdloebslut);
			merge databib.rk_Individdata&aar (drop=_TYPE_ rename=(_FREQ_=antal_laan))
				Individdata_ejd&aar (keep=pnr frivrd_alle frivrd_pribol frivrd_ejer frivrd_fritid boligvaerdi_pribol boligvaerdi_real);
			by pnr;

		/*If installment payments (afdrag) and interest payments are not computed for ALL the individual's loans, then the total installment and interest payment
			are set to missing*/
			if afdrag_for_antal     lt antal_laan then afdrag_for     = .;
			if afdrag_nu_antal      lt antal_laan then afdrag_nu      = .;
			if rente_bet_for_antal  lt antal_laan then rente_bet_for  = .;
			if rente_bet_nu_antal   lt antal_laan then rente_bet_nu   = .;


		/*For individuals with a loan where the interest-only period ends a specific year: find th loan, where the interest-only period ends the 
			specific year, and the LTV in the mortgaged property is the highest. Then finde the total remaining dept in that property. 
			Note: Not necessarily the property with the highest remaining dept (though that is the case, most of the times). 
			The prev. constructed help-variables is used to find this remaining dept
			*/

			label antal_laan                 = 'Number of active (udest�ende) loans (end of year) Antal udest�ende realkreditl�n ultimo �ret'
			      hovedobl                   = 'Principal on all loans (bond value) Principal p� alle l�n (obligationsv�rdi)'
			      hovedknt                   = 'Principal on al loans (cash value) Principal p� p� alle l�n (cash value)'
			      restobl                    = 'Total remaining dept (bond value) Remaining dept p� alle l�n (obligationsv�rdi)'
			      restknt                    = 'Total remaining dept (cash value) Remaining dept p� alle l�n (cash value)'
				  restobl_80				 = 'Share of RESTOBL with LTV above 80 Andel af restobl der ligger med LTV over 80'
			      restobl_rtl                = 'Remaining dept on loan with interest adjustment (bond value)'
			  	restobl_afdfri             = 'Remaining dept on interest-only loans (bond value)'
				restobl_rtlafdfri          = 'Remaining dept on loans with interest adjustment and interest-only (bond value)'
				restobl_rtl_tilp_1         = 'Remaining dept on loans with interest adjustment in less than 1 year (bond value)'
				restobl_rtl_tilp1          = 'Remaining dept dept on loans with interest adjustment in 1 year (bond value)'
				restobl_rtl_tilp2          = 'Remaining dept dept on loans with interest adjustment in 2 years (bond value)'
				restobl_rtl_tilp3          = 'Remaining dept dept on loans with interest adjustment in 3 years (bond value)'
				restobl_rtl_tilp4          = 'Remaining dept dept on loans with interest adjustment in 4 years (bond value)'
				restobl_rtl_tilp5          = 'Remaining dept dept on loans with interest adjustment in 5 years (bond value)'
				  restobl_FastRente 		 = 'Remaining dept with fixed interest'	 
				  restobl_VarRente			 = 'Remaining dept with variable interest'
				  restobl_F5				 = 'Remaining dept with 5 years fixed interest'
				  restobl_GE_F5				 = 'Remaining dept with 5 years or more fixed interest'

			      afdragsfrihed_for          = 'Number of loans with interest-only in the current year'
			      afdragsfrihed_nu           = 'Number of loans with interest-only in the following year'
			      
			      maxLTV					 = 'Highest LTV amoung all properties'
				  rentetilpas                = 'Number of loans with interest adjustment'
				  fastrente 				 = 'Number of fixed interest loans'	 
				  KUN_rentetilpas 			 = 'Number of loans with interest adjustment, with installment payments (being paid)'
				  KUN_afdragsfrihed 		 = 'Number of fixed interest interest-only loans'
				  KUN_fastrente				 = 'Number of fixed interest loans with installment payments'
			      AF_for_RT                  = 'Number of loans with interest adjustment and interest-only in the current year'
			      AF_nu_RT                   = 'Number of loans with interest adjustment and interest-only in the following year'
			      F1                         = 'Number of F1-loans'
			      F3                         = 'Number of F3-loans'
				  F5                         = 'Number of F5-loans'
			      optag0310                  = 'Number of loans taken in the period 2003-10'
			      optag0210                  = 'Number of loans taken in the period 2002-10'
				  RTtid_1                    = 'Number of loans with interest adjustment in less than 1 year'
			      RTtid1                     = 'Number of loans with interest adjustment in 1 year'
			      RTtid2                     = 'Number of loans with interest adjustment in 2 years'
				  RTtid3                     = 'Number of loans with interest adjustment in 3 years'
			      RTtid4                     = 'Number of loans with interest adjustment in 4 years'
			      RTtid5                     = 'Number of loans with interest adjustment in 5 years'
			      RTtid5_                    = 'Number of loans with interest adjustment in more than 5 years'
				  LTV_60                     = 'Number of loans in properties with LTV under 60%'
			      LTV60_80                   = 'Number of loans in properties with LTV 60-80%'
				  LTV80_100                  = 'Number of loans in properties with LTV 80-100%'
			      LTV100_                    = 'Number of loans in properties with LTV over 100%'
				  AFnu_LTV_60                = 'Number of interest-only loans (following �r) in properties with LTV under 60%'
			      AFnu_LTV60_80              = 'Number of interest-only loans (following �r) in properties with LTV 6-800%'
			      AFnu_LTV80_100             = 'Number of interest-only loans (following �r) in properties with LTV 80-100%'
			      AFnu_LTV100_               = 'Number of interest-only loans (following �r) in properties with LTV over 100%'
				  AFfor_LTV_60               = 'Number of interest-only loans (current �r) in properties with LTV under 60%'
			      AFfor_LTV60_80             = 'Number of interest-only loans (current �r) in properties with LTV 60-80%'
			      AFfor_LTV80_100            = 'Number of interest-only loans (current �r) in properties with LTV 80-100%'
			      AFfor_LTV100_              = 'Number of interest-only loans (current �r) in properties with LTV over 100%'
				  RT_LTV_60                  = 'Number of loans with interest adjustment with LTV under 60%'
			      RT_LTV60_80                = 'Number of loans with interest adjustment with LTV 60-80%'
			      RT_LTV80_100               = 'Number of loans with interest adjustment with LTV 80-100%'
			      RT_LTV100_                 = 'Number of loans with interest adjustment with LTV over 100%'
				  AFRTnu_LTV_60              = 'Number of interest-only loans with interest adjustment in properties with LTV under 60%'
			      AFRTnu_LTV60_80            = 'Number of interest-only loans with interest adjustment in properties with LTV 60-80%'
			      AFRTnu_LTV80_100           = 'Number of interest-only loans with interest adjustment in properties with LTV 80-100%'
			      AFRTnu_LTV100_             = 'Number of interest-only loans with interest adjustment in properties with LTV over 100%'
			      afdrag_for                 = 'Installment payment (afdrag) in mortgage in the current year'
			      afdrag_nu                  = 'Installment payment (afdrag) in mortgage in the following year'
				  afdrag_hyp                 = 'Hypothetical payment on interest-only loan, when the interest-only period ends'
			      rente_bet_for              = 'Interest payments on mortgages in current year'
			      rente_bet_nu               = 'Interest payments on mortgages in following year'
				  rente_bet_nu_fast          = 'Interest payments on fastforrentede mortgages in following year'
				  rente_bet_nu_variabel      = 'Interest payments on variabelt forrentede mortgages in following year'
				  bidrag                     = 'Yearly payment (bidrag)'
			      senest_optag               = 'Year the most recent loan was taken'
				  senest_optag_aar_maaned    = 'Year and month the most recent loan was taken'
				  senest_optag_restance		 = 'Year the most recent loan in arrears was taken'
				  tidligst_optag             = 'Year the eldest loan amoung current loans was taken'
			      senest_optag_pribol        = 'Year the most recent loan in primary residence was taken (not nec. "ejer")'
				  tidligst_optag_pribol      = 'Year the eldest loan amoung current loans in primary residence was taken (not nec. "ejer")'
			      senest_optag_ejer          = 'Year the most recent loan was taken in "owner" property'
				  senest_optag_res_ejer      = 'Year the most recent loan in arrears was taken in "owner" property'
				  tidligst_optag_ejer        = 'Year the eldest loan amoung current loans in "owner" property was taken'
				  rstsep45                   = '45-days arrears amount on September term (termin)'
				  rstjn105                   = '105-days arrears amount on June term (termin)'
				  LTV_pribol                 = 'LTV-value, primary residence (not nec. owner)'
				  frivrd_pribol              = 'Equity, primary residence (not nec. owner)'
				  frivrd_alle                = 'Equity, all properties'
			      frivrd_ejer                = 'Equity, "owner" properties'
				  frivrd_fritid              = 'Equity, secondary residece'
				  boligvaerdi_real			 = 'Property value, R.I. valuation'
				  boligvaerdi_pribol		 = 'Value of primary residence (not nec. owner), R.I. valuation';
		run;

		proc means data=databib.rk_individdata&aar;
			title "Variable overview, mortgage data for individuals, &aar";
		run;


	%END;
%mend;
%real5;		/* Runs macro*/
/* Deletes work data */
%macro slette_real6;
	%do aar = &startaar_real %to &startaar_real;
		proc datasets library = work;
			delete 	Individdata_ejd&aar Realkredit_ejd&aar restobl_ejd&aar;
		run;
	%end;
%mend;
%slette_real6;

